﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace ExcelExport
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        string excelPath;

        private void btnSelect_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                excelPath = openFileDialog1.FileName;
                lblExcelFile.Text = excelPath;
            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            if (excelPath == null)
            {
                MessageBox.Show("请先选择Excel文件");
                return;
            }

            try
            {
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                Workbook workbook = app.Workbooks.Open(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                try
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("{");

                    bool isFirst = true;

                    for (int i = 1; i < workbook.Worksheets.Count; i++)
                    //for (int i = 1; i < 2; i++)
                    {
                        Worksheet worksheet = (Worksheet)workbook.Worksheets[i + 1];
                        int row = 2;

                        while (true)
                        {
                            Range range = ((Range)worksheet.Cells[row, 1]);
                            if (range.Value == null)
                                break;

                            if (isFirst)
                            {
                                sb.Append(string.Format("\"{0}\":{{",range.Value));
                                isFirst = false;
                            }
                            else
                                sb.Append(string.Format(",\"{0}\":{{", range.Value));

                            sb.Append(string.Format("\"Key\":\"{0}\",", range.Value));

                            range = ((Range)worksheet.Cells[row, 2]);
                            if (range.Value != null)
                            {
                                try
                                {
                                    sb.Append(string.Format("\"Point\":\"{0}\",", Convert.ToInt32(range.Value.ToString()).ToString()));
                                }
                                catch
                                {
                                    sb.Append(string.Format("\"Point\":\"{0}\",", range.Value.ToString()));
                                }
                            }
                            else
                                sb.Append("\"Point\":\"\",");

                            range = ((Range)worksheet.Cells[row, 4]);
                            sb.Append(string.Format("\"Description\":\"{0}\",", range.Value));
                            
                            range = ((Range)worksheet.Cells[row, 5]);
                            string str = range.Value.ToString();
                            if (str == "1.0")
                                str = "1";
                            else
                                str = "0";
                            sb.Append(string.Format("\"Passive\":\"{0}\",", str));
                            
                            sb.AppendLine("}");

                            row++;
                        }
                    }

                    sb.AppendLine("}");


                    if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    {
                        Stream stream;
                        if (File.Exists(saveFileDialog1.FileName))
                            stream = File.OpenWrite(saveFileDialog1.FileName);
                        else
                            stream = File.Create(saveFileDialog1.FileName);

                        using (StreamWriter sw = new StreamWriter(stream))
                        {
                            sw.WriteLine(sb.ToString());
                        }

                        stream.Close();
                    }

                    MessageBox.Show("OK");

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    workbook.Close();
                }
            }
            catch (Exception ex1)
            {
                MessageBox.Show(ex1.Message);
            }
        }
    }
}
